CREATE PROCEDURE [dbo].[asi_FixDuplicateIndividualRows]
AS
BEGIN
    BEGIN TRANSACTION
    -- Fix up the problem caused by converting a "CM" record with two ContactMain records
    -- to a "M" record, which leaves two Individual records associated with the same Name record
    DECLARE @cleanupTable TABLE (ContactKey uniqueidentifier)
    DECLARE @udpateTable TABLE (ContactKey uniqueidentifier)

    INSERT INTO @cleanupTable  -- Get a list of extraneous individual records
    SELECT a.ContactKey FROM ContactMain a INNER JOIN ContactMain b ON a.SyncContactID = b.SyncContactID
    WHERE a.ContactKey != b.ContactKey AND a.IsInstitute = 0 AND b.IsInstitute = 0 AND a.SyncContactID != b.ID

    INSERT INTO @udpateTable  -- Get a list of individual record IDs that need to be updated
    SELECT a.ContactKey FROM ContactMain a INNER JOIN ContactMain b ON a.SyncContactID = b.SyncContactID
    WHERE a.ContactKey != b.ContactKey AND a.IsInstitute = 0 AND b.IsInstitute = 0 AND a.ID != b.SyncContactID

     -- Delete extraneous Individual record
    IF EXISTS (SELECT 1 FROM @cleanupTable)
    BEGIN
        UPDATE a
           SET ContactStatusCode = d.ContactStatusCode,
               UpdatedByUserKey = c.UserKey,
               UpdatedOn = GETDATE(),
               SyncContactID = NULL,
               ID = NULL
          FROM ContactMain a INNER JOIN @cleanupTable b ON a.ContactKey = b.ContactKey
                             INNER JOIN UserMain c ON c.UserId = 'MANAGER'
                             INNER JOIN ContactStatusRef d ON d.ContactStatusDesc = 'Delete' AND d.IsSystem = 1
    END

    -- Correct the ID on the remaining Individual record
    IF EXISTS (SELECT 1 FROM @udpateTable)
    BEGIN
        UPDATE a
           SET a.ID = a.SyncContactID
          FROM ContactMain a INNER JOIN @udpateTable b ON a.ContactKey = b.ContactKey
    END

    COMMIT TRANSACTION
END

GO